<?php


namespace app\service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class ExcelService
{
    protected $allowed_ext = ["xls", "xlsx","csv",];
    protected $map = [ 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];

    /**
     * 读取数据excel数据
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function readData($file): array
    {
        // 获取文件的后缀名，因视频从剪贴板里黏贴时后缀名为空，所以此处确保后缀一直存在
        $extension = strtolower($file->extension());
        if (!in_array($extension, $this->allowed_ext)) {
            return [
                'code' => -1,
                'msg' => '格式不正确',
            ];
        }
        // 转码
        $file = iconv('utf-8', 'gb2312', $file);
        $reader =IOFactory::createReader(ucfirst($extension));
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($file);
        $sheet = $spreadsheet->getSheet(0);   //excel中的第一张sheet
        return $sheet->toArray(null, true, true, true);

    }

    /**
     * 调整数据
     * @param $oldData
     * @return array
     */
    private function assembleKeyValue($oldData)
    {
        $keyAry =array();
        $DataAry =array();
        foreach ($oldData as $k=>$subData) {
            if ($k == 0) {
                $keyAry = $subData;
            } else {
                foreach ($subData  as $ke=>$value) {
                    if ($keyAry) {
                        if (array_key_exists($ke, $keyAry)) {
                            $DataAry[$k][ $keyAry[$ke]] = $value;
                        }
                    }
                }
            }
        }
        return $DataAry;
    }

    /**
     * 导出数据
     * @param array $header
     * @param array $data
     * @param string $tableName
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function export(array $header, array $data, string $tableName)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $index = 0;
        $map = $this->map;
        $str = '';

        foreach ($header as $k => $val) {
            $kk = $map[$index].'1';
            // 设置表头

            $sheet->setCellValue($kk, $k);

            // 设置列宽
            $sheet->getColumnDimension($map[$index])->setAutoSize(true);
            $index++;
        }
        $first = $map[0].'1';
        $last = $map[$index];
        //设置字体大小加粗
        $sheet->getStyle("$map[0]1:$map[$index]1")->getFont()->setName('宋体')->setSize(12)->setBold(true);
        // 遍历数据
        $i=1;
        foreach ($data as $key => $val) {
            $cols = ++$i;
            // 数据居中
            $sheet->getStyle("$first:$last$cols")->applyFromArray(
                array(
                    'alignment' => array(
                        'horizontal' => Alignment::HORIZONTAL_CENTER
                    )
                )
            );
            //配置边框样式
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => Border::BORDER_THICK,//粗边框
                    ),
                ),
            );

            //执行边框样式
            $sheet->getStyle("$first:$last$cols")->applyFromArray($styleArray);
            //数据内容垂直居中
            $sheet->getStyle("$first:$last$cols")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            //Excel的第A列，uid是你查出数组的键值，下面以此类推
            $kk = 0;
            foreach ($header as $key => $value) {
                $str .= "setCellValue($map[$kk]$cols, $val[$value])->";
                $res = $spreadsheet->setActiveSheetIndex(0)->setCellValue($map[$kk].$cols, $val[$value]);
                $kk++;
            }
            $str = trim($str, '->');
        }

        //设置sheet1操作表名
        $sheet->setTitle($tableName);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$tableName.date('Ymd').'.xlsx');
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->save('php://output');
        exit;
    }
}